package extractURLsFromTweets;
import java.sql.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.io.*;
import Utility.Utility;


/*
 * read in start tweet_id, end tweet_id and db number from file
 * find real starting id (lowest id in range that is not in the urls table already)
 * for all tweets in the range:
 *     extract
 *     write to db
 */

public class extractURLsFromTweets {
	
	private static int MAX_TWEETS = 100; //max tweets to select from database at one time
	private static int MAX_BATCH_SIZE = 50; //max number of urls to insert at a time
	private static int start_tweet_id = 0;
	private static int end_tweet_id = 0;
	private static int DB_num = 0;
	private static Connection conn = null;
	
	public static void init()
	{
		String db_name = "";
		try
		{
			BufferedReader input =  new BufferedReader(new FileReader("range.conf"));
			try 
			{
				start_tweet_id = Integer.valueOf(input.readLine());
				end_tweet_id = Integer.valueOf(input.readLine());
				DB_num = Integer.valueOf(input.readLine());
			}
			
			finally {
				input.close();
			}
		}
		catch(IOException e)
		{
			Utility.logMessage("ERROR: Could not read in initial range!", "error_extractURLsFromTweets.txt");
			System.exit(1);
		}
		
		try
		{
			db_name = "twitter" + String.valueOf(DB_num) + ".db";
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+db_name);
		}
		catch(SQLException e)
		{
			Utility.logMessage("ERROR: Could not open DB: " + db_name + "! " + e.getMessage(), "error_extractURLsFromTweets.txt");
			System.exit(1);
		}
		catch(ClassNotFoundException e)
		{
			Utility.logMessage("ERROR: " + e.getMessage(), "error_extractURLsFromTweets.txt");
			System.exit(1);
		}
		
		getRealStartIndex();
	}
	
	public static void getRealStartIndex()
	{
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			try
			{
				stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			    rs = stmt.executeQuery("SELECT tweet_table_id FROM urls WHERE tweet_table_id > " + String.valueOf(start_tweet_id) +
			    		" AND tweet_table_id < " + String.valueOf(end_tweet_id) + " ORDER BY tweet_table_id desc limit 1;");
				rs.next();
				if (!rs.isAfterLast()) 
				{
					start_tweet_id = rs.getInt(1);
				}
			}
			catch(SQLException e)
			{
				Utility.logMessage("ERROR: " + e.getMessage(), "error_extractURLsFromTweets.txt");
			}
			finally
			{
				rs.close();
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			Utility.logMessage("ERROR: " + e.getMessage(), "error_extractURLsFromTweets.txt");
		}
	}
	
	public static String extractURL(String text) {
		String httpURL = "http://";
		String tripW = "www.";
		String URLinText = "";
		int idxStart = text.indexOf(httpURL);
		boolean noHttp = false;
		if(idxStart == -1){
			idxStart = text.indexOf(tripW);
			if(idxStart != -1)
				noHttp = true;
		}
		if(idxStart != -1){
			text = text.substring(idxStart);
			int idxEnd = text.indexOf(" ");
			if(idxEnd == -1){
				idxEnd = text.indexOf("\n");
			}
			if(idxEnd == -1){
				idxEnd = text.indexOf("\t");
			}
			if(idxEnd == -1){
				idxEnd = text.indexOf("\r");
			}
			if(idxEnd != -1){
				URLinText =  text.substring(0, idxEnd);
			}
			else{
				URLinText =  text;
			}
		}
		if(noHttp){
			URLinText = httpURL + URLinText;
		}
		return URLinText;
	}
	
	public static String getRealURL(String shortURL) throws Exception {
		String realURL = "";
		URL serverAddress = null;         
		try {         
			serverAddress = new URL(shortURL);
			HttpURLConnection connection = (HttpURLConnection)serverAddress.openConnection();
			connection.setReadTimeout(1500);
			connection.connect();
			connection.getResponseCode();			
			realURL = connection.getURL().toString();
		}
		catch(Exception e){
			e.printStackTrace();
			Utility.logMessage(e.getMessage(), "error_extractURLsFromTweets.txt");
		}
		if (realURL.equals("")) {
			realURL = shortURL;
		}
		return realURL;
	}
	
	public static void extractURLs(int start, int end) {
		try {
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection("jdbc:sqlite:twitter.db");
			Statement stmt = conn.createStatement();
			ResultSet srs = stmt.executeQuery("SELECT id, tweet_id, user_id, text FROM tweets WHERE id >= " + start + " AND id < " + end);
			String instURLMap = "insert into urls(tweet_table_id, url, status) VALUES(?,?,?);";
			
			PreparedStatement psInstURLMap = conn.prepareStatement(instURLMap);
			int batchSize = 0;
			int totalSize = 0;
			String text = "";
			String URLinText = "";
			String realURL = "";
			while(srs.next()) {
				text = srs.getString("text");
				URLinText = extractURL(text);
				if(!URLinText.equals("") ) 
				{
					if(URLinText.length() < 50)
					{
						realURL = getRealURL(URLinText);
					}
					else
					{
						realURL = URLinText;
					}
					System.out.println(URLinText + " -> " +realURL);
					
					int tweet_table_id = srs.getInt("id");
					psInstURLMap.setInt(1, tweet_table_id);
					psInstURLMap.setString(2, realURL);
					psInstURLMap.setString(3, "?");
					psInstURLMap.addBatch();
					batchSize++;
					totalSize++;
					
					if(batchSize == MAX_BATCH_SIZE) {
						psInstURLMap.executeBatch();
						psInstURLMap.clearBatch();
						batchSize = 0;
					}
				}
				
			}
			psInstURLMap.executeBatch();
			psInstURLMap.close();
			stmt.close();
			srs.close();
			conn.close();
		}
		catch(SQLException e){
			e.printStackTrace();
			Utility.logMessage("ERROR: extracting from " + start + " to " + end + e.getMessage(), "error_extractURLsFromTweets.txt");
		}
		catch(Exception e){
			e.printStackTrace();
			Utility.logMessage("ERROR: extracting from " + start + " to " + end + e.getMessage(), "error_extractURLsFromTweets.txt");
		}
	}	
	
	public static void LastIDLogMessage(String message) {
		try{
			BufferedWriter f = new BufferedWriter(new FileWriter("extractURLsLastID_LOG", true));
			f.write(message + "\n");			
			f.close();
		}
		catch(IOException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		init();
		try 
		{
			int current_id = start_tweet_id;
		    while(true) 
		    {
		    	//if we are at the end, finish up
		    	if ((current_id+MAX_TWEETS) >= end_tweet_id)
		    	{
		    		extractURLs(current_id, end_tweet_id);
		    		break;
		    	}
		    	//otherwise, just do the next 100
		    	else 
		    	{
			    	extractURLs(current_id, current_id + MAX_TWEETS);
			    	current_id += MAX_TWEETS;
			    	Utility.logMessage("Starting: " + String.valueOf(start_tweet_id) +
			    			           "\nEnding: " + String.valueOf(end_tweet_id) +
			    			           "\nCurrent: " + String.valueOf(current_id), 
			    			           "status_extractURLsFromTweets.txt");
		    	}
		    }		    
		}
		catch(Exception e) {
			e.printStackTrace();
			Utility.logMessage("Error in main" + e.getMessage(), "error_extractURLsFromTweets.txt");
		}
	}
}
